var express = require('express');
var router = express.Router();

/* GET users listing. */
router.get('/', function (req, res, next) {
  res.send('respond with a resource');
});

//解析表单提交的数据
router.use(express.urlencoded({
  extended: false
}));
//加载mysql数据库
let mysql = require("mysql");
//配置mysql数据库
let config = {
  host: "localhost", //数据库连接服务
  port: 3306, //数据库端口
  user: "root", //数据库用户名
  password: "", //数据库密码
  database: "student", //选择要操作的库房
}
//连接数据库
let connection = mysql.createConnection(config);
//判断是否连接成功
connection.connect((err) => {
  if (err) {
    console.log("连接数据库失败")
  } else {
    console.log("数据库连接成功")
  }
})

//渲染首页页面
router.get("/index", (req, res) => {
  //查询数据
  let sql = `select * from stu_user`;
  connection.query(sql, (err, doc) => {
    if (err) {
      console.log("查询失败")
    } else {
      res.render("xueyemian/index.html", {
        doc: doc
      });
    }
  })
})

//渲染添加页面
router.get("/add", (req, res) => {
  res.render("xueyemian/add.html")
})
//处理表单数据
router.post("/add", (req, res) => {
  //获取表单数据
  //console.log(req.body)
  let {
    name,
    sex,
    age
  } = req.body;
  let sql = `insert into stu_user (name,sex,age) value ("${name}","${sex}",${age})`;
  connection.query(sql, (err) => {
    if (err) {
      res.render("xueyemian/wait.html", {
        wait: 2, //等待时间
        content: "添加失败",
        href: "/users/add"
      })
    } else {
      res.render("xueyemian/wait.html", {
        wait: 2, //等待时间
        content: "添加成功",
        href: "/users/index"
      })
    }
  })
})

//删除数据
router.get("/del", (req, res) => {
  //更加id删除 
  //获取当前点击的id值
  let id = req.query.id;
  //console.log(id);
  let sql = `delete from stu_user where id=${id}`;
  connection.query(sql, (err) => {
    if (err) {
      //res.send("删除失败")
      res.render("xueyemian/wait.html", {
        wait: 2, //等待时间
        content: "删除失败",
        href: "/users/index"
      })
    } else {
      //res.send("删除成功")
      res.render("xueyemian/wait.html", {
        wait: 2, //等待时间
        content: "删除成功",
        href: "/users/index"
      })
    }
  })
})

//修改数据  查询对应的数据
router.get("/update", (req, res) => {
  //查询数据
  let id = req.query.id;
  let sql = `select * from stu_user where id=${id}`;
  connection.query(sql, (err, doc) => {
    if (err) {
      console.log("查询数据失败")
    } else {
      console.log(doc);
      res.render("xueyemian/edit.html", {
        result: doc
      })
    }
  })
})
//处理表单
router.post("/update", (req, res) => {
  //获取表单提交的数据
  let {
    name,
    sex,
    age,
    id
  } = req.body;
  //console.log(name, sex, age, id);
  let sql = `update stu_user set name="${name}",sex="${sex}",age=${age} where id=${id}`
  //console.log(sql);
  connection.query(sql, (err) => {
    if (err) {
      res.render("xueyemian/wait.html", {
        wait: 2, //等待时间
        content: "修改失败",
        href: '/users/index'
      })
    } else {
      res.render("xueyemian/wait.html", {
        wait: 2, //等待时间
        content: "修改成功",
        href: "/users/index"
      })
    }
  })
})

module.exports = router;